/*
* @Author: steven.wang
* @Date:   2019-01-17 11:47:33
* @Last Modified by:   steven.wang
* @Last Modified time: 2019-05-15 14:52:10
*/
const mysql = require('mysql');
const dbConfig = require('../config').mysql;

const pool = mysql.createPool({
	user: dbConfig.user,
	password: dbConfig.password,
	database: dbConfig.database,
	host: dbConfig.host,
	port: dbConfig.port
});

const query = (sql, values) => new Promise((resolve, reject) => {
	pool.getConnection((err, connection) => {
		if (err) {
			reject(err);
		} else {
			console.log('=======================================================');
			console.log(sql);
			console.log('-------------------------------------------------------');
			console.log(JSON.stringify(values));
			console.log('=======================================================');
			connection.query(sql, [values], (err, rows) => {
				if (err) {
					reject(err);
				}  else {
					resolve(rows);
				}
				connection.release();
			});
		}
	});
});

const generateAddSql = data => {
	let keys = [], values = [];
	for (let key in data.conditions[0]) {
		keys.push(key);
	}
	data.conditions.forEach(item => {
		let temp = [];
		for (let key of keys) {
			temp.push(item[key]);
		}
		values.push(temp);
	});
	let sql = 'insert into ' + data.table + '(' + keys.join(',') + ') values ?';
	let sqlData = values;
	return {
		sql,
		sqlData
	}
}
const generateDeleteSql = data => {
	let sql = 'delete from ' + data.table + '', sqlData = [],
		sqlTemp = '';
	for (let key in data.conditions) {
		let condItem = data.conditions[key];
		if (condItem.length == 1) {
			sqlTemp += ' and ' + key + ' = ?';
			sqlData.push(condItem[0]);
		} else if(condItem.length == 2) {
			sqlTemp += ' and ' + key + ' between ? and ?';
			sqlData.push(condItem[0]);
			sqlData.push(condItem[1]);
		}
	}
	sql += sqlTemp ? (' where' + sqlTemp.substring(4)) : '';
	return {
		sql,
		sqlData
	}
}
const generateUpdateSql = data => {
	let sql = 'update ' + data.table + '', sqlData = [],
		sqlTemp = '', sqlWhere = '';
	for (let key in data.wants) {
		sqlTemp += key + ' = ?,';
		sqlData.push(data.wants[key])
	}
	for (let key in data.conditions) {
		sqlWhere += ' and ' + key + ' = ?';
		sqlData.push(data.conditions[key]);
	}
	sql += sqlTemp ? ' set ' + sqlTemp.substring(0, sqlTemp.length - 1) : '';
	sql += sqlWhere ? ' where' + sqlWhere.substring(4) : '';
	return {
		sql,
		sqlData
	}
}
const generateSearchSql = data => {
	let sql = 'select ' + data.wants + ' from ' + data.table + '', sqlData = [],
		sqlTemp = '';
	if (data.conditions) {
		for (let key in data.conditions) {
			let condItem = data.conditions[key];
			if (condItem.length == 1) {
				sqlTemp += condItem[0] ? ' and ' + key + ' = ?' : '';
				condItem[0] && sqlData.push(condItem[0]);
			} else if(condItem.length == 2) {
				sqlTemp += ' and ' + key + ' between ? and ?';
				sqlData.push(condItem[0]);
				sqlData.push(condItem[1]);
			}
		}
	}
	if (data.likes) {
		for (let key in data.likes) {
			let likeItem = data.likes[key];
			if (likeItem) {
				sqlTemp += ' and ' + key + ' like "%' + likeItem + '%"';
			}
		}
	}
	sql += sqlTemp ? ' where' + sqlTemp.substring(4, sqlTemp.length ) : '';
	sql += data.sorts ? ' order by ' + data.sorts : '';
	sql += data.pages ? ' limit ' + (data.pages.page - 1)*data.pages.pageSize + ',' + data.pages.pageSize : '';
	return {
		sql,
		sqlData
	}
}

const generateSql = (data) => {
	switch (data.action) {
		case 'add':
			return generateAddSql(data);
			break;
		case 'delete':
			return generateDeleteSql(data);
			break;
		case 'update':
			return generateUpdateSql(data);
			break;
		case 'search':
			return generateSearchSql(data);
			break;
		default:
			return {
				sql: 'show databases',
				sqlData: []
			}
			break;
	}
}

module.exports.query = query;
module.exports.generateSql = generateSql;